import uuid
from datetime import datetime
from typing import Optional
from app.utils.mysql_helper import MySQLHelper
import logging

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')


class TranslationPointTransaction:
    """
    翻译积分交易模型
    用于记录用户每次执行翻译消耗的积分明细
    """
    
    def __init__(self, auto_commit=False):
        """
        初始化翻译积分交易模型
        """
        self.db_helper = MySQLHelper(auto_commit=auto_commit)
        self._ensure_table_exists()
    
    def _ensure_table_exists(self):
        """
        确保翻译积分交易表存在
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                # 检查translation_logs表是否存在，使用更安全的方式
                try:
                    cursor.execute("SELECT 1 FROM translation_logs LIMIT 1")
                    cursor.fetchall()  # 读取结果，避免Unread result错误
                except Exception:
                    # translation_logs表可能不存在，继续执行创建当前表的操作
                    pass
                
                # 创建translation_point_transactions表，使用更宽松的外键策略
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS translation_point_transactions (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    transaction_id VARCHAR(36) NOT NULL,
                    shop_domain VARCHAR(255) NOT NULL,
                    translation_log_id VARCHAR(36) NOT NULL,
                    points_consumed INT NOT NULL,
                    source_language VARCHAR(50) NOT NULL,
                    target_language VARCHAR(50) NOT NULL,
                    text_length INT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_shop_domain (shop_domain),
                    INDEX idx_transaction_id (transaction_id),
                    INDEX idx_translation_log_id (translation_log_id),
                    INDEX idx_created_at (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
                """)
            conn.commit()
        except Exception as e:
            logging.error(f"创建翻译积分交易表失败: {e}")
            if conn:
                try:
                    conn.rollback()
                    # 如果表已存在但没有索引，添加索引
                    with conn.cursor() as cursor:
                        cursor.execute("ALTER TABLE translation_point_transactions ADD INDEX IF NOT EXISTS idx_shop_domain (shop_domain)")
                        cursor.execute("ALTER TABLE translation_point_transactions ADD INDEX IF NOT EXISTS idx_transaction_id (transaction_id)")
                        cursor.execute("ALTER TABLE translation_point_transactions ADD INDEX IF NOT EXISTS idx_translation_log_id (translation_log_id)")
                        cursor.execute("ALTER TABLE translation_point_transactions ADD INDEX IF NOT EXISTS idx_created_at (created_at)")
                        conn.commit()
                except:
                    pass
            raise
    
    def create_transaction(
        self,
        shop_domain: str,
        translation_log_id: str,
        points_consumed: int,
        source_language: str,
        target_language: str,
        text_length: int
    ) -> str:
        """
        创建翻译积分交易记录
        
        Args:
            shop_domain: 店铺域名
            translation_log_id: 关联的翻译日志ID
            points_consumed: 消耗的积分数量
            source_language: 源语言
            target_language: 目标语言
            text_length: 翻译文本长度
            
        Returns:
            str: 交易ID
        """
        try:
            transaction_id = str(uuid.uuid4())
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                cursor.execute("""
                INSERT INTO translation_point_transactions 
                (transaction_id, shop_domain, translation_log_id, points_consumed, 
                 source_language, target_language, text_length)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                """,
                (transaction_id, shop_domain, translation_log_id, points_consumed,
                 source_language, target_language, text_length))
            conn.commit()
            logging.info(f"创建翻译积分交易记录成功: {transaction_id}")
            return transaction_id
        except Exception as e:
            logging.error(f"创建翻译积分交易记录失败: {e}")
            if conn:
                conn.rollback()
            raise
    
    def get_transaction_by_id(self, transaction_id: str) -> dict:
        """
        根据交易ID获取交易记录
        
        Args:
            transaction_id: 交易ID
            
        Returns:
            dict: 交易记录
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                cursor.execute(
                    "SELECT * FROM translation_point_transactions WHERE transaction_id = %s",
                    (transaction_id,)
                )
                return cursor.fetchone()
        except Exception as e:
            logging.error(f"获取翻译积分交易记录失败: {e}")
            raise
    
    def get_shop_transaction_history(
        self,
        shop_domain: str,
        limit: int = 50,
        offset: int = 0
    ) -> list:
        """
        获取店铺的翻译积分交易历史
        
        Args:
            shop_domain: 店铺域名
            limit: 返回记录数量限制
            offset: 偏移量
            
        Returns:
            list: 交易历史记录列表
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                # 先获取交易记录，不进行表连接
                cursor.execute(
                    """
                    SELECT transaction_id, translation_log_id, points_consumed,
                           source_language, target_language, text_length,
                           created_at
                    FROM translation_point_transactions
                    WHERE shop_domain = %s
                    ORDER BY created_at DESC
                    LIMIT %s OFFSET %s
                    """,
                    (shop_domain, limit, offset)
                )
                return cursor.fetchall()
        except Exception as e:
            logging.error(f"获取店铺翻译积分交易历史失败: {e}")
            raise
    
    def get_total_points_consumed(self, shop_domain: str) -> int:
        """
        获取店铺翻译总消耗积分
        
        Args:
            shop_domain: 店铺域名
            
        Returns:
            int: 总消耗积分
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                cursor.execute(
                    """
                    SELECT COALESCE(SUM(points_consumed), 0) 
                    FROM translation_point_transactions 
                    WHERE shop_domain = %s
                    """,
                    (shop_domain,)
                )
                result = cursor.fetchone()
                return result[0] if result else 0
        except Exception as e:
            logging.error(f"获取店铺翻译总消耗积分失败: {e}")
            raise
    
    def get_daily_consumption(
        self,
        shop_domain: str,
        days: int = 30
    ) -> list:
        """
        获取店铺最近N天的每日翻译积分消耗
        
        Args:
            shop_domain: 店铺域名
            days: 统计天数
            
        Returns:
            list: 每日消耗记录
        """
        try:
            conn = self.db_helper._get_connection()
            with conn.cursor() as cursor:
                cursor.execute(
                    """
                    SELECT DATE(created_at) as date,
                           SUM(points_consumed) as points_consumed,
                           COUNT(*) as translation_count
                    FROM translation_point_transactions
                    WHERE shop_domain = %s
                    AND created_at >= DATE_SUB(NOW(), INTERVAL %s DAY)
                    GROUP BY DATE(created_at)
                    ORDER BY date DESC
                    """,
                    (shop_domain, days)
                )
                return cursor.fetchall()
        except Exception as e:
            logging.error(f"获取每日翻译积分消耗失败: {e}")
            raise


# 创建模型实例
translation_point_transaction_model = TranslationPointTransaction()